/*
  FILENAME	: setup_filedef.sql
  Copyright (C) 2013  Testmonger
  See https://inroad.codeplex.com/license

  NOTES		: 
    - param 1 number of iterations
	- always keep record 1 as default in the case running this proc directly.
	- control record or any other rules must be run before this proc to prepare file temp table.
	- the select statement must match FileTemp columns.

  	select * from FileDefColumns
    select * from PersonTemp
	exec setup_filedef "E:\APPS\InRoad\FileDef\person_def.csv"
*/


IF OBJECT_ID(N'setup_filedef') IS NOT NULL 
BEGIN
	DROP procedure setup_filedef
END
GO

CREATE PROCEDURE setup_filedef
	@filedef varchar(100)
AS
BEGIN
	DECLARE @tabname nchar(60), @vsql nvarchar(1000), @cnt1 int, @cnt2 int,
		@fcolname nchar(30), @dtype nchar(10), @dlen nchar(10)

	-- need to rename file columns
	TRUNCATE table FileDefColumns

	EXEC load_insub 'FileDefColumns',@filedef

	-- add check for only one file name
	select @tabname = rtrim(filename)+'TEMP' from FileDefColumns
	where counter = (select min(counter) from FileDefColumns)

	-- drop table
	set @vsql = 'IF OBJECT_ID(N'''+rtrim(@tabname)+''') IS NOT NULL DROP table '+rtrim(@tabname)
	BEGIN
		--EXEC master..xp_cmdshell @vsql --, no_output
		execute( @vsql )
	END
	-- debug
	--select @vsql

	select @cnt1 = count(*), @cnt2 = 1 from FileDefColumns

	set @vsql = 'CREATE TABLE [dbo].['+rtrim(@tabname)+']( '

	--debug
	--select @cnt1 , @cnt2,@tabname

	WHILE @cnt2 < @cnt1
	BEGIN
		select @fcolname = rtrim(columnname) , @dtype = rtrim(ColDataType),
		  @dlen = rtrim(ColLength)
		from FileDefColumns where counter = @cnt2

		IF @dtype in ('nchar')
			set @vsql = @vsql+rtrim(@fcolname)+' '+rtrim(@dtype)+'('+rtrim(@dlen)+') ,'
		--set @vsql = @vsql+@fcolname+' [nchar](30),'
		IF @dtype in ('int','float')
			set @vsql = @vsql+rtrim(@fcolname)+' '+rtrim(@dtype)+' ,'
		
		set @cnt2=@cnt2+1
		--debug
		--select @cnt2, @vsql
	END
		select @fcolname = rtrim(columnname) , @dtype = rtrim(ColDataType),
		  @dlen = rtrim(ColLength)
		from FileDefColumns where counter = @cnt1

		IF @dtype in ('nchar')
			set @vsql = @vsql+rtrim(@fcolname)+' '+rtrim(@dtype)+'('+rtrim(@dlen)+') '
		--set @vsql = @vsql+@fcolname+' [nchar](30),'
		IF @dtype in ('int','float')
			set @vsql = @vsql+rtrim(@fcolname)+' '+rtrim(@dtype)+' '
	
	set @vsql = @vsql+' )'

	-- create table
	BEGIN
--		EXEC master..xp_cmdshell @vsql --, no_output 
		execute( @vsql )
	END

	--debug
	--select @cnt1 , @cnt2,@tabname
	--select @vsql

/*
    select * from PersonTemp
	exec setup_filedef "E:\APPS\InRoad\FileDef\person_def.csv"
	
	CREATE TABLE [dbo].[FileDefColumns](
	[FileName] [nchar](60) NOT NULL,
	[ColumnName] [nchar](30) NOT NULL,
	[ColDataType] [nchar](10) NOT NULL,
	[ColLength] [nchar](3) NULL,
	[ColComment] [nchar](100) NULL
)
*/
/*
	select @outfile = rtrim(OutFilePath)+rtrim(OutFileName)+'.'+rtrim(OutFileExt)
	from FileDefinition where FileID = @filedef

	--set @vsql = 'bcp "select rtrim(C_ID), rtrim(DECode), rtrim(TxnDate), rtrim(DEVal), rtrim(DENum)'
	--set @vsql = @vsql+' FROM '+DB_NAME()+'.dbo.FileTemp " queryout "'+@outfile+'" -c -T -t, -S '+@@SERVERNAME
	
	select @vsql = 'bcp "'+dbo.ufnTableQryRTRIM(@filedef)+'" queryout "'+@outfile+'" -c -T -t, -S '+@@SERVERNAME+' -d '+DB_NAME()

	select @vsql , @outfile

	-- 	exec write_file 1

	BEGIN
		EXEC master..xp_cmdshell @vsql , no_output 
	END
*/
END


/*
CREATE TABLE [dbo].[PersonTEMP](AcctNum [nchar](30),DataCode [nchar](30),IntVal [nchar](30),CharVal [nchar](30),DateVal [nchar](30),FloatVal [nchar](30) )

*/
